package cn.innve.demo;

import net.coobird.thumbnailator.Thumbnails;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.util.IOUtils;

import java.io.*;
import java.util.Random;

public class PoiDemo {
    PoiDemo() {

    }

    void single() {
        HSSFWorkbook workbook = new HSSFWorkbook();

        // Note that sheet name is Excel must not exceed 31 characters
        // and must not contain any of the any of the following characters:
        // 0x0000
        // 0x0003
        // colon (:)
        // backslash (\)
        // asterisk (*)
        // question mark (?)
        // forward slash (/)
        // opening square bracket ([)
        // closing square bracket (])
        String safeSheetName = WorkbookUtil.createSafeSheetName("相册");
        Sheet sheet = workbook.createSheet(safeSheetName);

        info(sheet);

        // 设置列宽默认值以适用于数据表格以外的列（照片部分）
        sheet.setDefaultColumnWidth(8 * 3);

        String[] titles = new String[7];
        titles[0] = "姓名";
        titles[1] = "出生日期";
        titles[2] = "电话";
        titles[3] = "报名渠道";
        titles[4] = "报名赛区";
        titles[5] = "当前排名";
        titles[6] = "人气";

        // 标题行样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());

        // 标题行
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);

            cell.setCellStyle(titleStyle);

            sheet.setColumnWidth(i, 8 * 2 * 256);
        }

        // 数据行样式
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 数据行
        for (int i = 0; i < 5; i++) {
            Row dataRow = sheet.createRow(1 + i);
            for (int j = 0; j < titles.length; j++) {
                Cell cell = dataRow.createCell(j);
                cell.setCellValue(titles[j] + i);

                cell.setCellStyle(dataStyle);
            }

            dataRow.setHeight((short) 4000);
        }

        CreationHelper helper = workbook.getCreationHelper();
        // Create the drawing patriarch.  This is the top level container for all shapes.
        Drawing drawing = sheet.createDrawingPatriarch();

        InputStream in = null;
        try {
            for (int i = 0; i < 5; i++) {
                for (int j = 0; j < 10; j++) {
                    Random random = new Random();
                    String name = "images/image" + (random.nextInt(10) + 1);

                    // 使用第三方库压缩图片
                    File file = new File(name + ".tmp.jpg");
                    Thumbnails.of(name + ".jpg").width(300).keepAspectRatio(true).toFile(file);

                    in = new FileInputStream(file);
                    byte[] bytes = IOUtils.toByteArray(in);
                    in.close();

                    file.delete();

                    int index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                    ClientAnchor anchor = helper.createClientAnchor();

                    anchor.setCol1(j + titles.length);
                    anchor.setRow1(i + 1);
                    anchor.setCol2(j + 1 + titles.length);
                    anchor.setRow2(i + 2);
                    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

                    drawing.createPicture(anchor, index);
                }
            }
        } catch (IOException ex) {
            ex.printStackTrace();
            return;
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }

        FileOutputStream out = null;

        try {
            out = new FileOutputStream("single.xls");
            workbook.write(out);
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    void sheet() {
        HSSFWorkbook workbook = new HSSFWorkbook();

        String players[] = new String[5];
        players[0] = "张三(13800138000)";
        players[1] = "李四(13800138001)";
        players[2] = "王五(13800138002)";
        players[3] = "赵六(13800138003)";
        players[4] = "孙七(13800138004)";

        // Note that sheet name is Excel must not exceed 31 characters
        // and must not contain any of the any of the following characters:
        // 0x0000
        // 0x0003
        // colon (:)
        // backslash (\)
        // asterisk (*)
        // question mark (?)
        // forward slash (/)
        // opening square bracket ([)
        // closing square bracket (])

        for (int x = 0; x < players.length; x++) {
            String safeSheetName = WorkbookUtil.createSafeSheetName(players[x]);
            Sheet sheet = workbook.createSheet(safeSheetName);

            info(sheet);

            // 设置行高默认值以适用于数据表格以外的行（照片部分）
            sheet.setDefaultRowHeight((short) (16 * 255));

            sheet.setDefaultColumnWidth(10);

            String[] titles = new String[7];
            titles[0] = "姓名";
            titles[1] = "出生日期";
            titles[2] = "电话";
            titles[3] = "报名渠道";
            titles[4] = "报名赛区";
            titles[5] = "当前排名";
            titles[6] = "人气";

            // 标题行样式
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setBorderLeft(BorderStyle.THIN);
            titleStyle.setBorderRight(BorderStyle.THIN);
            titleStyle.setBorderTop(BorderStyle.THIN);
            titleStyle.setBorderBottom(BorderStyle.THIN);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());

            // 标题行
            Row titleRow = sheet.createRow(0);
            titleRow.setHeight((short) 255);
            for (int i = 0; i < titles.length; i++) {
                Cell cell = titleRow.createCell(i);
                cell.setCellValue(titles[i]);

                cell.setCellStyle(titleStyle);
            }

            // 数据行样式
            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setBorderLeft(BorderStyle.THIN);
            dataStyle.setBorderRight(BorderStyle.THIN);
            dataStyle.setBorderTop(BorderStyle.THIN);
            dataStyle.setBorderBottom(BorderStyle.THIN);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 数据行
            Row dataRow = sheet.createRow(1);
            dataRow.setHeight((short) 255);
            for (int j = 0; j < titles.length; j++) {
                Cell cell = dataRow.createCell(j);
                cell.setCellValue(titles[j] + j);

                cell.setCellStyle(dataStyle);
            }

            CreationHelper helper = workbook.getCreationHelper();
            // Create the drawing patriarch.  This is the top level container for all shapes.
            Drawing drawing = sheet.createDrawingPatriarch();

            InputStream in = null;
            try {
                for (int j = 0; j < 10; j++) {
                    Random random = new Random();
                    String name = "images/image" + (random.nextInt(10) + 1);

                    // 使用第三方库压缩图片
                    File file = new File(name + ".tmp.jpg");
                    Thumbnails.of(name + ".jpg").width(300).keepAspectRatio(true).toFile(file);

                    in = new FileInputStream(file);
                    byte[] bytes = IOUtils.toByteArray(in);
                    in.close();

                    file.delete();

                    int index = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                    ClientAnchor anchor = helper.createClientAnchor();

                    anchor.setCol1(2 * (j >= 5 ? j - 5 : j));
                    anchor.setRow1(2 + (j >= 5 ? 1 : 0));
                    anchor.setCol2(2 * (j >= 5 ? j - 5 : j) + 2);
                    anchor.setRow2(3 + (j >= 5 ? 1 : 0));
                    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

                    drawing.createPicture(anchor, index);
                }
            } catch (IOException ex) {
                ex.printStackTrace();
                return;
            } finally {
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }

        FileOutputStream out = null;

        try {
            out = new FileOutputStream("sheet.xls");
            workbook.write(out);
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (IOException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    void info(Sheet sheet) {
        int defaultColumnWidth = sheet.getDefaultColumnWidth();
        short defaultRowHeight = sheet.getDefaultRowHeight();

        System.out.println(String.format("defaultColumnWidth:%s, defaultRowHeight:%s", defaultColumnWidth, defaultRowHeight));

        float defaultRowHeightInPoints = sheet.getDefaultRowHeightInPoints();

        System.out.println(String.format("defaultRowHeightInPoints:%s", defaultRowHeightInPoints));
    }

    public static void main(String[] args) {
        PoiDemo demo = new PoiDemo();
        //demo.single();
        demo.sheet();
    }
}
